﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Pub;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;

namespace LZQXT.Test.Forms.DB
{
    public partial class FDBRow : Form
    {

        List<object> lst1 = new List<object>();
        List<TextBox> tbox = new List<TextBox>();
        List<Label> lab = new List<Label>();
        List<Button> btnCtrl = new List<Button>();

        public string sTabName = "";
        public string strSQL_Key = "";
        public ADOTest ado1 = null;
        public string s_Enter = "";
        int i_tbox_width = 200;

        public const int i_TYP_EDIT_TREE = 1;
        public const int i_TYP_EDIT_GRID = 2;
        public int i_typ_edit = 0;

        public class DataRowII
        {
            public string Text;
            public string Name;
            public Type type;
        }

        /// <summary>
        /// 主键信息
        /// </summary>
        public List<DataRowII> lstDr_Key = new List<DataRowII>();
        public List<DataRowII> lstDr = new List<DataRowII>();

        public TextEditorControl tboxSQL;
        public CTextEditMenu[] textBox_Menu;
        public List<TextEditorControl> lstTextBox = new List<TextEditorControl>();

        public FDBRow()
        {
            InitializeComponent();
        }

        private void FDBRow_Load(object sender, EventArgs e)
        {
            tabControl1.Dock = DockStyle.Fill;
            panel_Edit.Dock = DockStyle.Fill;
            groupBox5.Dock = DockStyle.Fill;

            tabCtrlSQL.Dock = DockStyle.Fill;

            tbox_Sql_Add.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Add.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Add.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Edt.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Edt.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Edt.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Del.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Del.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Del.Encoding = System.Text.Encoding.Default;

            tbox_Sql_Sel.BorderStyle = BorderStyle.Fixed3D;
            tbox_Sql_Sel.Document.HighlightingStrategy = HighlightingStrategyFactory.CreateHighlightingStrategy("TSQL");
            tbox_Sql_Sel.Encoding = System.Text.Encoding.Default;

            lstTextBox.Clear();
            lstTextBox.Add(tbox_Sql_Add);
            lstTextBox.Add(tbox_Sql_Edt);
            lstTextBox.Add(tbox_Sql_Del);
            lstTextBox.Add(tbox_Sql_Sel);

            textBox_Menu = new CTextEditMenu[4];
            textBox_Menu[0] = new CTextEditMenu(tbox_Sql_Add);
            textBox_Menu[1] = new CTextEditMenu(tbox_Sql_Edt);
            textBox_Menu[2] = new CTextEditMenu(tbox_Sql_Del);
            textBox_Menu[3] = new CTextEditMenu(tbox_Sql_Sel);

            tbox_Sql_Add.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Edt.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Del.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;
            tbox_Sql_Sel.ActiveTextAreaControl.TextArea.KeyDown += tbox_KeyDown;

        }

        /// <summary>
        /// 刷新数据及sql脚本
        /// </summary>
        public void field_Ref(ADOTest ado1, string sTabName, object obj_data1, int i_RowIndex)
        {
            CDebug.EnterMethod("加载数据");

            this.ado1 = ado1;
            this.sTabName = sTabName;

            try
            {
                i_tbox_width = trackBarLen.Value; //Convert.ToInt16(tboxLen.Text);
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }

            try
            {
                foreach (object obj1 in lst1)
                {
                    if (obj1 is Label) ((Label)obj1).Dispose();
                    if (obj1 is Button) ((Button)obj1).Dispose();
                    if (obj1 is TextBox) ((TextBox)obj1).Dispose();
                }

                lstDr = getDr(ado1, obj_data1, i_RowIndex);

                panel_Edit.Visible = false;

                #region 获取主键

                List<DataColumn> dc_Key = getKey(ado1, sTabName);

                lstDr_Key.Clear();
                if (dc_Key.Count > 0)
                {
                    for (int i = 0; i < dc_Key.Count; i++)
                    {
                        for (int k = 0; k < lstDr.Count; k++)
                        {
                            if (dc_Key[i].Caption == lstDr[k].Name)
                            {
                                lstDr_Key.Add(lstDr[k]);
                            }
                        }
                    }
                }
                else
                {
                    lstDr_Key = lstDr;
                }
                #endregion

                #region 创建文本框

                int i_ColCount = lstDr.Count;
                //lab = new Label[i_ColCount];
                //tbox = new TextBox[i_ColCount];
                //删除多余的文本框
                for (int i = tbox.Count - 1; i >= lstDr.Count; i--)
                {
                    tbox[i].Dispose();
                    tbox.RemoveAt(i);

                    lab[i].Dispose();
                    lab.RemoveAt(i);
                }

                for (int i = 0; i < i_ColCount; i++)
                {

                    //生成标签
                    if (lab.Count <= i || lab[i] == null)
                    {
                        Label lab1 = new Label();
                        lab.Add(lab1);
                        lab[i].Parent = panel_Edit;
                    }

                    if (lab[i].Text != lstDr[i].Name) lab[i].Text = lstDr[i].Name;
                    //lst1.Add(lab[i]);

                    //生成文本框
                    if (tbox.Count <= i || tbox[i] == null)
                    {
                        TextBox tbox1 = new TextBox();
                        tbox.Add(tbox1);
                        tbox[i].Parent = panel_Edit;
                    }

                    tbox[i].Text = "";

                    if (tbox[i].Text != lstDr[i].Text) tbox[i].Text = lstDr[i].Text;

                    //lst1.Add(tbox[i]);
                }

                #endregion

                #region 生成按钮

                if (btnCtrl.Count == 0)
                {
                    string[] sCap = new string[] { "添加", "更新", "删除", "刷新" };
                    for (int i = 0; i < 4; i++)
                    {
                        if (btnCtrl.Count <= i || btnCtrl[i] == null)
                        {
                            Button btn = new Button();
                            btnCtrl.Add(btn);
                            btnCtrl[i].Tag = 10 + i;
                            btnCtrl[i].Top = 1;
                            btnCtrl[i].Left = i * 90 + 200;
                            btnCtrl[i].Width = 80;
                            btnCtrl[i].Text = sCap[i];
                            btnCtrl[i].Parent = panel4;
                            btnCtrl[i].Click += btn_Click;
                        }
                    }
                }


                #endregion

                //调整文本框位置
                tbox_Resize();

                panel_Edit.Visible = true;

                sql_Ref();

            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                CDebug.Send("生成数据显示界面出错", ex);
            }

            CDebug.ExitMethod("加载数据");
        }

        private string getVal(DataRowII dr)
        {
            if (dr.type == typeof(Int32))
                return dr.Text;
            else if (dr.type == typeof(String))
                return "'" + dr.Text + "'";
            else if (dr.type == typeof(Byte[]))
                return "NULL";
            else
                return "'" + dr.Text + "'";
        }

        private void btn_Click(object sender, EventArgs e)
        {
            int i_btn = 0;

            if (sender is Int32) i_btn = Convert.ToInt32(sender);
            if (sender is MenuItem) i_btn = Convert.ToInt32(((MenuItem)sender).Tag);
            if (sender is ToolStripMenuItem) i_btn = Convert.ToInt32(((ToolStripMenuItem)sender).Tag);
            if (sender is Button) i_btn = Convert.ToInt32(((Button)sender).Tag);
            if (sender is ToolStripButton) i_btn = Convert.ToInt32(((ToolStripButton)sender).Tag);

            string strSQL = "";
            try
            {
                switch (i_btn)
                {
                    case 1:     //fill TreeView


                        break;
                    case 2:     // 执行sql语句

                        //strSQL = textBox_Menu[tabCtrlSQL.SelectedIndex].SelectedText;
                        //if (strSQL == "")
                        //{
                        if (tabCtrlSQL.SelectedIndex == 0) strSQL = tbox_Sql_Add.Text;
                        else if (tabCtrlSQL.SelectedIndex == 1) strSQL = tbox_Sql_Edt.Text;
                        else if (tabCtrlSQL.SelectedIndex == 2) strSQL = tbox_Sql_Del.Text;
                        else if (tabCtrlSQL.SelectedIndex == 3) strSQL = tbox_Sql_Sel.Text;
                        //}

                        CDebug.Send(strSQL);
                        if (CPub.MsgBox("确定要执行改语句吗?", "执行") == true)
                            ado1.cmd1(strSQL);

                        break;
                    case 10:        //add node

                        strSQL = getSQL_Insert();
                        if (CPub.MsgBox("确定要添加新的数据到数据库吗？", "添加"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 11:        //edit node
                        strSQL = getSQL_Update();
                        if (CPub.MsgBox("确定要写入到数据库吗？", "修改"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 12:       //delete node
                        strSQL = getSQL_Delete();
                        if (CPub.MsgBox("确定要删除吗？", "删除"))
                        {
                            if (sTabName == "")
                                MessageBox.Show("表名不能为空");
                            else
                                ado1.cmd1(strSQL);
                        }
                        break;
                    case 13:        //refresh node
                        sql_Ref();
                        break;

                }
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
                CDebug.Send(ex);
                MessageBox.Show(ex.ToString());
            }

        }

        /// <summary>
        /// 重新生成sql语句
        /// </summary>
        public void sql_Ref()
        {
            tbox_Sql_Add.Text = "";
            tbox_Sql_Edt.Text = "";
            tbox_Sql_Del.Text = "";
            tbox_Sql_Sel.Text = "";
            tbox_Sql_Add.Text = getSQL_Insert();
            tbox_Sql_Edt.Text = getSQL_Update();
            tbox_Sql_Del.Text = getSQL_Delete();
            tbox_Sql_Sel.Text = getSQL_Select();
        }

        /// <summary>
        /// 在菜单中添加新记录
        /// </summary>
        /// <returns></returns>
        public string getSQL_Insert()
        {
            string strSQL_Add = "";
            string sCol = "";
            string sVal = "";
            string sColS = "";
            string sValS = "";

            for (int i = 0; i < tbox.Count; i++)
            {
                //if (dr != null && tbox[i].Text == "") continue;
                //if (dr == null) continue;

                if (lstDr[i].type == typeof(Byte[])) continue;
                //CDebug.Send(lstDr[i].type.Name);
                try
                {
                    if (i > 0)
                    {
                        sColS += ", " + s_Enter;
                        sValS += ", " + s_Enter;
                    }

                    if (cbox_Col.Checked) sCol = "[" + lstDr[i].Name + "]";
                    else sCol = lstDr[i].Name;

                    if (lstDr[i].type == typeof(Int32))
                    {
                        sVal = tbox[i].Text;
                        if (sVal == "") sVal = "0";
                    }
                    else if (lstDr[i].type == typeof(String))
                    {
                        sVal = "'" + tbox[i].Text.Replace("'", "''") + "'";
                    }
                    else
                    {
                        sVal = "'" + tbox[i].Text + "'";
                    }

                    sColS += sCol;
                    sValS += sVal;
                }
                catch (System.Exception ex)
                {
                    CDebug.Send(ex.ToString());
                    CDebug.SendError("向结果集中赋值时出错", ex);
                }
            }

            strSQL_Add = "INSERT INTO " + sTabName + "(" + sColS + ") VALUES (" + sValS + ")" + s_Enter;
            return strSQL_Add;
        }

        /// <summary>
        /// 修改菜单中的记录
        /// </summary>
        /// <returns></returns>
        public string getSQL_Update()
        {
            string strSQL_Edt = "UPDATE " + sTabName + " SET " + s_Enter;
            string sCol = "";
            string sVal = "";

            for (int i = 0; i < tbox.Count; i++)
            {
                if (lstDr[i].type == typeof(Byte[])) continue;

                if (i > 0) strSQL_Edt += ", " + s_Enter;

                //数值
                if (lstDr[i].type == typeof(Int32))
                {
                    sVal = tbox[i].Text;
                    if (tbox[i].Text == "")
                        sVal = "0";
                }
                else if (lstDr[i].type == typeof(String))
                {
                    sVal = "'" + tbox[i].Text.Replace("'", "''") + "'";
                }
                else
                {
                    sVal = "'" + tbox[i].Text + "'";
                }

                //字段名
                if (cbox_Col.Checked) sCol = "[" + lstDr[i].Name + "]";
                else sCol = lstDr[i].Name;

                strSQL_Edt += sCol + "=" + sVal;

            }

            strSQL_Key = getStrSQL_Key(lstDr_Key);
            strSQL_Edt += s_Enter + " WHERE " + strSQL_Key + s_Enter;
            return strSQL_Edt;
        }

        /// <summary>
        /// 删除菜单项
        /// </summary>
        /// <returns></returns>
        public string getSQL_Delete()
        {
            strSQL_Key = getStrSQL_Key(lstDr_Key);
            string strSQL_Del = "DELETE FROM " + sTabName + s_Enter + " WHERE " + strSQL_Key + s_Enter;
            return strSQL_Del;
        }

        /// <summary>
        /// 生成查询语句
        /// </summary>
        /// <returns></returns>
        public string getSQL_Select()
        {
            string strSQL_Sel = "SELECT ";
            string sCol = "";

            for (int i = 0; i < tbox.Count; i++)
            {

                if (i > 0) strSQL_Sel += ", " + s_Enter;

                if (cbox_Col.Checked) sCol = "[" + lstDr[i].Name + "]";
                else sCol = lstDr[i].Name;

                strSQL_Sel += sCol;
            }

            strSQL_Key = getStrSQL_Key(lstDr_Key);
            strSQL_Sel += " FROM " + sTabName + s_Enter + " WHERE " + strSQL_Key + s_Enter;
            return strSQL_Sel;
        }

        /// <summary>
        /// 查询主键字段
        /// </summary>
        /// <param name="ado1"></param>
        /// <param name="sTabName"></param>
        /// <returns></returns>
        public List<DataColumn> getKey(ADOTest ado1, string sTabName)
        {
            List<DataColumn> lstDc = new List<DataColumn>();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序 ");
            strSQL.Append("from sysindexes i ");
            strSQL.Append("join sysindexkeys k on i.id = k.id and i.indid = k.indid ");
            strSQL.Append("join sysobjects o on i.id = o.id ");
            strSQL.Append("join syscolumns c on i.id=c.id and k.colid = c.colid ");
            strSQL.Append("where o.xtype = 'U' ");
            strSQL.Append("and exists(select 1 from sysobjects where xtype = 'PK' and name = ");
            strSQL.Append("i.name) and o.name='" + sTabName + "' ");//--表名称
            strSQL.Append("order by o.name,k.colid ");
            DataSet ds_key = ado1.sel1(strSQL.ToString());

            DataSet ds1 = ado1.sel1("SELECT TOP 1 * FROM " + sTabName);

            for (int i = 0; i < ds_key.Tables[0].Rows.Count; i++)
            {
                DataRow dr_key = ds_key.Tables[0].Rows[i];
                for (int j = 0; j < ds1.Tables[0].Columns.Count; j++)
                {
                    DataColumn dc = ds1.Tables[0].Columns[j];
                    if (dr_key["字段名"].ToString() == dc.Caption)
                    {
                        lstDc.Add(dc);
                    }
                }
            }
            return lstDc;
        }

        public string getStrSQL_Key(List<DataRowII> lstDr_Key)
        {
            strSQL_Key = "";
            for (int i = 0; i < lstDr_Key.Count; i++)
            {
                if (lstDr_Key[i].Text != "")
                {
                    if (i > 0 && strSQL_Key != "") strSQL_Key += s_Enter + " AND ";

                    string strCol = lstDr_Key[i].Name;
                    if (cbox_Col.Checked) strCol = "[" + strCol + "]";

                    if (lstDr_Key[i].type == typeof(Int32))
                        strSQL_Key += strCol + "=" + lstDr_Key[i].Text;
                    else
                        strSQL_Key += strCol + "='" + lstDr_Key[i].Text + "'";
                }
            }

            if (strSQL_Key == "") strSQL_Key = "1=1";
            return strSQL_Key;
        }

        /// <summary>
        /// 返回列名、数值和数据类型
        /// </summary>
        /// <param name="ado1"></param>
        /// <param name="obj_data1"></param>
        /// <param name="i_RowIndex"></param>
        /// <returns></returns>
        public List<DataRowII> getDr(ADOTest ado1, object obj_data1, int i_RowIndex)
        {
            List<DataRowII> lstDr = new List<DataRowII>();
            //DataGridView
            if (obj_data1 is DataGridView)
            {
                DataGridView dg1 = (DataGridView)obj_data1;
                DataGridViewColumnCollection dgCol = dg1.Columns;

                //字段名，数据类型
                for (int i = 0; i < dgCol.Count; i++)
                {
                    DataRowII dr = new DataRowII();
                    dr.Name = dgCol[i].Name;
                    dr.type = dgCol[i].CellType;
                    lstDr.Add(dr);
                }

                //数值
                if (dg1.Rows.Count > 0 && i_RowIndex < dg1.Rows.Count)
                {
                    DataGridViewRow dgr = dg1.Rows[i_RowIndex];
                    for (int i = 0; i < dgCol.Count; i++)
                    {
                        lstDr[i].Text = dgr.Cells[i].Value.ToString();
                    }
                }
            }
            //DataSet
            else if (obj_data1 is DataSet)
            {
                DataSet ds1 = (DataSet)obj_data1;
                DataColumnCollection dsCol = ds1.Tables[0].Columns;

                for (int i = 0; i < dsCol.Count; i++)
                {
                    DataRowII dr = new DataRowII();

                    dr.Name = dsCol[i].Caption;
                    dr.type = dsCol[i].DataType;
                    lstDr.Add(dr);
                }

                if (ds1.Tables[0].Rows.Count > 0 && i_RowIndex < ds1.Tables[0].Rows.Count)
                {
                    DataRow dsr = ds1.Tables[0].Rows[i_RowIndex];

                    for (int i = 0; i < dsCol.Count; i++)
                    {
                        lstDr[i].Text = dsr[i].ToString().Trim();
                    }
                }
            }
            return lstDr;
        }

        private void FDBRow_FormClosing(object sender, FormClosingEventArgs e)
        {
            e.Cancel = true;
            this.Hide();
        }

        /// <summary>
        /// 切换换行
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cbox_Enter_CheckedChanged(object sender, EventArgs e)
        {
            //检查换行
            sql_Ref_Enter(cbox_Enter.Checked);
        }

        /// <summary>
        /// 字段名方括号切换
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cbox_Col_CheckedChanged(object sender, EventArgs e)
        {
            //字段名带方括号
            sql_Ref();
        }

        /// <summary>
        /// 换行开关
        /// </summary>
        /// <param name="b_Enter"></param>
        private void sql_Ref_Enter(Boolean b_Enter)
        {
            if (b_Enter)
            {
                s_Enter = "\n";
                for (int i = 0; i < lstTextBox.Count; i++)
                {
                    lstTextBox[i].Text = lstTextBox[i].Text.Replace(",", ",\n");
                    //lstTextBox[i].Text = lstTextBox[i].Text.Replace("AND", "\nAND");
                }
            }
            else
            {
                s_Enter = "";
                for (int i = 0; i < lstTextBox.Count; i++)
                {
                    lstTextBox[i].Text = lstTextBox[i].Text.Replace("\n", "");
                }
            }
            this.Refresh();
        }

        private void tbox_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                btn_Click(btnRun, null);
            }
        }

        private void FDBRow_Shown(object sender, EventArgs e)
        {

        }

        private void tabPage2_Enter(object sender, EventArgs e)
        {

        }

        private void FDBRow_KeyDown(object sender, KeyEventArgs e)
        {

        }


        private void FDBRow_Resize(object sender, EventArgs e)
        {
            //调整文本框位置
            tbox_Resize();
        }

        public void tbox_Resize()
        {

            //调整文本框位置
            int i_X = 0;
            int i_Y = 0;
            int i_h = 0;
            int i_lab_w = 0;  //130
            int i_lab_len = 0;
            int i_tbox_h = 25;
            int i_panel_h = panel_Edit.Height;

            int j = 0;

            for (int i = 0; i < lab.Count; i++)
            {
                lab[i].AutoSize = true;
                i_lab_w = Math.Max(lab[i].Width, i_lab_w);
                i_tbox_h = Math.Min(tbox[i].Height, i_tbox_h);
            }

            i_X = i_tbox_width + i_lab_w + 30;

            if (tbox != null)
            {
                int i_ColCount = tbox.Count;
                Label l_tem = new Label();
                l_tem.Parent = panel_Edit;

                for (int i = 0; i < i_ColCount; i++)
                {
                    i_Y = 25 * i_h;
                    if (i_Y + 38 > i_panel_h)
                    {
                        i_h = 0;
                        i_Y = 25 * i_h;
                        j++;
                    }
                    i_h++;

                    lab[i].Left = 10 + (i_X * j);
                    lab[i].Top = i_Y;
                    lab[i].Width = i_lab_w;

                    tbox[i].Left = i_lab_w + (i_X * j) + 15;
                    tbox[i].Top = i_Y;
                    tbox[i].Width = i_tbox_width;
                    tbox[i].Multiline = false;

                    l_tem.Text = tbox[i].Text;
                    l_tem.AutoSize = true;
                    int i_l = l_tem.Width / i_tbox_width;           //多于一行
                    if (i_l > 0)
                    {
                        i_h += i_l;
                        int i_tem1 = (i_l + 1) * i_tbox_h;          //未对齐高度
                        i_tem1 = (i_tem1 / 25 + 1) * 25 - 5;        //对齐后高度

                        if (i_panel_h < i_Y + i_tem1)       //不能超出下方
                        {
                            i_tem1 = i_panel_h - i_Y;
                            i_tem1 = (i_tem1 / 25) * 25 - 5;
                        }

                        tbox[i].Height = i_tem1;
                        tbox[i].Multiline = true;
                        tbox[i].ScrollBars = ScrollBars.Vertical;
                    }


                }

                l_tem.Dispose();
            }

        }

        private void trackBarLen_Scroll(object sender, EventArgs e)
        {
            try
            {
                i_tbox_width = trackBarLen.Value; //Convert.ToInt16(tboxLen.Text);
            }
            catch (System.Exception ex)
            {
                CDebug.SendError(ex.ToString());
            }

            tbox_Resize();
        }
    }
}
